from typing import Optional
from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

data_my_safe_03 = APIRouter(prefix="/phone", tags=[], dependencies=[Depends(oauth2_scheme)])


class CabinetAbnormalLoginSearch(PaginationBase):
    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    fbegdate: str = Field("2000-01-01", description="开始日期(yyyy-MM-dd)，默认'2000-01-01'")
    fenddate: str = Field("2000-01-01", description="结束日期(yyyy-MM-dd)，默认'2000-01-01'")
    fpointNo: str = Field("", description="点位编号")


@data_my_safe_03.get("getExceUseAlarm", summary="储物柜-异常登录查询接口")
def get_exceuse_alarm(cabient: CabinetAbnormalLoginSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_tc_usewarring a,am_sys_user b, i_ts_box c,a_ts_point d, a_ts_site e"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.flogindate,'yyyymmdd') between  :ftakedateStart and  :ftakedateEnd "
    param["ftakedateEnd"] = cabient.fenddate
    param["ftakedateStart"] = cabient.fbegdate
    sql = sql + " and a.user_id = b.id"
    sql = sql + " and c.fequipmentbarcode = a.fequipmentbarcode"
    sql = sql + " and c.fpoint_no = d.fpoint_no"
    sql = sql + " and d.fsite_no = e.fsite_no"
    if cabient.fsiteNo != "":
        sql = sql + " and e.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabient.fsiteNo
    if cabient.fpointNo != "":
        sql = sql + " and c.fpoint_no= :fpointNo "
        param["fpointNo"] = cabient.fpointNo

    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select to_char(a.flogindate,'yyyy-mm-dd hh24:mi:ss') flogindate"
    sql = sql + " ,e.fsite_name,d.fpoint_name,c.fequipmenttag,b.username,a.fnote,d.fsite_no"
    sql = sql + " from i_tc_usewarring a,am_sys_user b, i_ts_box c,a_ts_point d, a_ts_site e"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.flogindate,'yyyymmdd') between  :ftakedateStart and  :ftakedateEnd "
    param["ftakedateEnd"] = cabient.fenddate
    param["ftakedateStart"] = cabient.fbegdate
    sql = sql + " and a.user_id = b.id"
    sql = sql + " and c.fequipmentbarcode = a.fequipmentbarcode"
    sql = sql + " and c.fpoint_no = d.fpoint_no"
    sql = sql + " and d.fsite_no = e.fsite_no"
    if cabient.fsiteNo != "":
        sql = sql + " and e.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabient.fsiteNo
    if cabient.fpointNo != "":
        sql = sql + " and c.fpoint_no= :fpointNo "
        param["fpointNo"] = cabient.fpointNo
    if cabient.order == "desc":
        sql = sql + " order by  flogindate desc "
    else:
        sql = sql + " order by  flogindate asc "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = cabient.limit
    param["offset"] = cabient.offset

    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['flogindate'] = data[key][0]
        d1['fsiteName'] = data[key][1]
        d1['fpointName'] = data[key][2]
        d1['fequipmenttag'] = data[key][3]
        d1['username'] = data[key][4]
        d1['fnote'] = data[key][5]
        d1['fsiteNo'] = data[key][6]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": cabient.limit, "page": cabient.pageIndex,
                 "pages": (count + cabient.limit - 1) // cabient.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
